#!/usr/bin/env python
# -*- coding: utf-8 -*-
# File  :continued_provision.py
# Time  :2025/5/13 14:32
# Email :fortunatedong@163.com
# Author:by Adonli
import os
import re
from datetime import datetime, timedelta

import pandas as pd
import openpyxl
from pathlib import Path

import toml

from config import ROOT_DIR
from utils.business_task.provision_excel_model import ProvisionExcelModel

# pd.set_option('display.max_rows', None)
# pd.set_option('display.max_columns', None)
# pd.set_option('display.width', None)
class ContinuedProvision:
    def __init__(self,file_path,now_date,brand):
        self.file_path = file_path
        self.now_date = now_date
        self.brand = brand
        if self.brand == 'zi':
            self.brand_name = 'Zi'
        if self.brand == 'zong':
            self.brand_name = 'Zong'
    def format_datas(self,sheets):
        # 设置未来行为选项（解决fillna警告）
        pd.set_option('future.no_silent_downcasting', True)
        # print(df1.columns)
        dtype_rules = {
            '首次报备日期': 'datetime',
            '理光接受报备日期（最新）': 'datetime',
            '3到期日期（空档期起点）': 'datetime',
            '销售员': 'str',
            '销售员2': 'str',
            '分支机构': 'str',
            'MIF': 'str',
            '开票客户名称': 'str',
            '最终用户名称': 'str',
            '联系人': 'str',
            '联系电话': 'str',
            '客户地址': 'str',
            '报备机型': 'str',
            '台数': 'str',
            '预测金额': 'str',
            '目前使用机器品牌': 'str',
            '机型及配置': 'str',
            '是否为理光MIF': 'str',
            '服务商': 'str',
            '预算部门': 'str',
            '使用部门': 'str',
            '1续备情况': 'datetime',
            '2续备情况': 'datetime',
            '重报（空档期终点）': 'datetime',
            # '续备情况': 'str',
            # '报备SI': 'str',
            # '目前使用机器品牌.1': 'str',
        }

        # 定义日期列
        date_columns = ['入职日期', '最后登录时间']

        # 自定义缺失值标识
        default_na = ['无数据', '--', '未知', '', ' ', ]
        # 默认缺失值标识
        na_values = ['', '#N/A', '#N/A N/A', '#NA', '-1.#IND', '-1.#QNAN',
                     '-NaN', '-nan', '1.#IND', '1.#QNAN', 'NA', 'NULL',
                     'NaN', 'n/a', 'nan', 'null', '缺失', '无']

        # 合并用户自定义缺失值
        final_na_values = default_na + (na_values if na_values else [])

        # 读取Excel文件（先全部作为对象类型读取）
        df = pd.read_excel(
            self.file_path,
            sheet_name=sheets,
            dtype='object',  # 先全部读取为对象类型
            na_values=final_na_values,
            keep_default_na=False,
            engine='openpyxl',  # 兼容.xlsx和.xlsm
            # verbose=True
        )
        df.fillna('', inplace=True)
        df = df.infer_objects(copy=False)
        # 应用数据类型规则
        if dtype_rules:
            for col, dtype in dtype_rules.items():
                if col in df.columns:
                    try:
                        if dtype == 'str':
                            df[col] = df[col].astype(str)
                        elif dtype == 'float':
                            df[col] = pd.to_numeric(df[col], errors='coerce')
                        elif dtype == 'int':
                            df[col] = pd.to_numeric(df[col], errors='coerce').astype('Int64')
                        elif dtype in ['datetime', 'timestamp']:
                            df[col] = pd.to_datetime(df[col], errors='coerce')
                        elif dtype == 'date':
                            df[col] = pd.to_datetime(df[col], errors='coerce').dt.date
                    except Exception as e:
                        print(f'转换列 {col} 到 {dtype} 时出错: {str(e)}')
                        continue

        # 处理日期列
        if date_columns:
            for col in date_columns:
                if col in df.columns:
                    try:
                        df[col] = pd.to_datetime(df[col], errors='coerce')
                    except Exception as e:
                        print(f'转换日期列 {col} 时出错: {str(e)}')
                        continue
        return df
    def provision_read_infoamtion(self):
        new_first_time_provision_df = self.format_datas("普通理光新报备")
        second_time_provision_df = self.format_datas("第一次续报完成")
        third_time_provision_df = self.format_datas("第二次续报完成")
        provision_file_name_list = []

        # new_provision_df = new_first_time_provision_df[new_first_time_provision_df["首次报备日期"] == self.now_date]
        # if not new_provision_df.empty:
        #     print(f"{self.now_date}有{len(new_provision_df)}个新客户需要首次报备")
        #
        #     for index, row in new_provision_df.iterrows():
        #         provision_date = row["首次报备日期"].strftime('%Y-%m-%d')
        #         invoicing_customer_name = row["开票客户名称"]
        #         latest_customer_name = row["最终用户名称"]
        #         nvoicing_customer_province = row["省"]
        #         nvoicing_customer_city = row["市"]
        #         installation_address_province = row["省"]
        #         installation_address_city = row["市"]
        #         installation_address_district = row["区"]
        #         address = row["客户地址"]
        #         brand = row["分支机构"]
        #         customer_distributor = row["销售员"]
        #         telephone = row["联系电话"]
        #         liaison = row["联系人"]
        #         budget_department = row["预算部门"]
        #         use_department = row["使用部门"]
        #         other_brand = ""
        #         service_agent_name = ""
        #         row20_prepare_recommended_models_accessories = row["报备机型"]
        #         row21_number = row["台数"]
        #         row21_amount = row["预测金额"]
        #         row23_delivery_date = ""
        #         row23_declarations_number = ""
        #         remarks = ""
        #         ProvisionExcelModel(self.brand).to_excel_file(provision_date=provision_date,
        #                                             invoicing_customer_name=invoicing_customer_name,
        #                                             latest_customer_name=latest_customer_name,
        #                                             nvoicing_customer_province=nvoicing_customer_province,
        #                                             nvoicing_customer_city=nvoicing_customer_city,
        #                                             installation_address_province=installation_address_province,
        #                                             installation_address_city=installation_address_city,
        #                                             installation_address_district=installation_address_district,
        #                                             address=address, brand=brand,
        #                                             customer_distributor=customer_distributor, telephone=telephone,
        #                                             liaison=liaison,
        #                                             budget_department=budget_department, use_department=use_department,
        #                                             other_brand=other_brand,
        #                                             service_agent_name=service_agent_name,
        #                                             row20_prepare_recommended_models_accessories=row20_prepare_recommended_models_accessories,
        #                                             row21_number=row21_number, row21_amount=row21_amount,
        #                                             row23_delivery_date=row23_delivery_date,
        #                                             row23_declarations_number=row23_declarations_number,
        #                                             remarks=remarks)
        #         new_latest_customer_name = re.sub(r'[\\/*?:<>|,.，。、"]', '_', latest_customer_name)
        #         file_name = f"{self.now_date}+{brand}+{new_latest_customer_name}.xlsx"
        #         provision_file_name_list.append(file_name)
        #         print(f"{file_name}")
        # else:
        #     print(f"{self.now_date}没有新的客户需要首次报备")

        # new_cycle_provision_df = new_first_time_provision_df[(new_first_time_provision_df["理光接受报备日期（最新）"] == self.now_date) & (new_first_time_provision_df["理光接受报备日期（最新）"] > new_first_time_provision_df["首次报备日期"])]
        # if not new_cycle_provision_df.empty:
        #     print(f"{self.now_date}有{len(new_cycle_provision_df)}个客户需要周期性首次续报备")
        #     for index, row in new_cycle_provision_df.iterrows():
        #         provision_date = row["理光接受报备日期（最新）"].strftime('%Y-%m-%d')
        #         invoicing_customer_name = row["开票客户名称"]
        #         latest_customer_name = row["最终用户名称"]
        #         nvoicing_customer_province = row["省"]
        #         nvoicing_customer_city = row["市"]
        #         installation_address_province = row["省"]
        #         installation_address_city = row["市"]
        #         installation_address_district = row["区"]
        #         address = row["客户地址"]
        #         brand = row["分支机构"]
        #         customer_distributor = row["销售员"]
        #         telephone = row["联系电话"]
        #         liaison = row["联系人"]
        #         budget_department = row["预算部门"]
        #         use_department = row["使用部门"]
        #         other_brand = ""
        #         service_agent_name = ""
        #         row20_prepare_recommended_models_accessories = row["报备机型"]
        #         row21_number = row["台数"]
        #         row21_amount = row["预测金额"]
        #         row23_delivery_date = ""
        #         row23_declarations_number = ""
        #         remarks = ""
        #         ProvisionExcelModel(self.brand).to_excel_file(provision_date=provision_date,
        #                                             invoicing_customer_name=invoicing_customer_name,
        #                                             latest_customer_name=latest_customer_name,
        #                                             nvoicing_customer_province=nvoicing_customer_province,
        #                                             nvoicing_customer_city=nvoicing_customer_city,
        #                                             installation_address_province=installation_address_province,
        #                                             installation_address_city=installation_address_city,
        #                                             installation_address_district=installation_address_district,
        #                                             address=address, brand=brand,
        #                                             customer_distributor=customer_distributor, telephone=telephone,
        #                                             liaison=liaison,
        #                                             budget_department=budget_department, use_department=use_department,
        #                                             other_brand=other_brand,
        #                                             service_agent_name=service_agent_name,
        #                                             row20_prepare_recommended_models_accessories=row20_prepare_recommended_models_accessories,
        #                                             row21_number=row21_number, row21_amount=row21_amount,
        #                                             row23_delivery_date=row23_delivery_date,
        #                                             row23_declarations_number=row23_declarations_number,
        #                                             remarks=remarks)
        #         new_latest_customer_name = re.sub(r'[\\/*?:<>|,.，。、"]', '_', latest_customer_name)
        #         file_name = f"{self.now_date}+{brand}+{new_latest_customer_name}.xlsx"
        #         provision_file_name_list.append(file_name)
        #         print(f"{file_name}")
        # else:
        #     print(f"{self.now_date}没有客户需要周期性首次续报备")

        first_provision_df = new_first_time_provision_df[new_first_time_provision_df["1续备情况"] == self.now_date]
        if not first_provision_df.empty:
            print(f"{self.now_date}有{len(first_provision_df)}个客户需要第一次续报备")
            for index, row in first_provision_df.iterrows():
                provision_date = row["1续备情况"].strftime('%Y-%m-%d')
                invoicing_customer_name = row["开票客户名称"]
                latest_customer_name = row["最终用户名称"]
                nvoicing_customer_province = row["省"]
                nvoicing_customer_city = row["市"]
                installation_address_province = row["省"]
                installation_address_city = row["市"]
                installation_address_district = row["区"]
                address = row["客户地址"]
                brand = row["分支机构"]
                customer_distributor = row["销售员"]
                telephone = row["联系电话"]
                liaison = row["联系人"]
                budget_department = row["预算部门"]
                use_department = row["使用部门"]
                other_brand = ""
                service_agent_name = ""
                row20_prepare_recommended_models_accessories = row["报备机型"]
                row21_number = row["台数"]
                row21_amount = row["预测金额"]
                row23_delivery_date = ""
                row23_declarations_number = ""
                remarks = ""
                ProvisionExcelModel(self.brand).to_excel_file(provision_date=provision_date,
                                                    invoicing_customer_name=invoicing_customer_name,
                                                    latest_customer_name=latest_customer_name,
                                                    nvoicing_customer_province=nvoicing_customer_province,
                                                    nvoicing_customer_city=nvoicing_customer_city,
                                                    installation_address_province=installation_address_province,
                                                    installation_address_city=installation_address_city,
                                                    installation_address_district=installation_address_district,
                                                    address=address, brand=brand,
                                                    customer_distributor=customer_distributor, telephone=telephone,
                                                    liaison=liaison,
                                                    budget_department=budget_department, use_department=use_department,
                                                    other_brand=other_brand,
                                                    service_agent_name=service_agent_name,
                                                    row20_prepare_recommended_models_accessories=row20_prepare_recommended_models_accessories,
                                                    row21_number=row21_number, row21_amount=row21_amount,
                                                    row23_delivery_date=row23_delivery_date,
                                                    row23_declarations_number=row23_declarations_number,
                                                    remarks=remarks)
                new_latest_customer_name = re.sub(r'[\\/*?:<>|,.，。、"]', '_', latest_customer_name)
                file_name = f"{self.now_date}+{brand}+{new_latest_customer_name}.xlsx"
                provision_file_name_list.append(file_name)
                print(f"{file_name}")
        else:
            print(f"{self.now_date}没有客户需要第一次续报备")


        second_provision_df = second_time_provision_df[second_time_provision_df["2续备情况"] == self.now_date]
        if not second_provision_df.empty:
            print(f"{self.now_date}有{len(second_provision_df)}个客户需要第二次续报备")

            for index,row in second_provision_df.iterrows():
                provision_date = row["2续备情况"].strftime('%Y-%m-%d')
                invoicing_customer_name = row["开票客户名称"]
                latest_customer_name = row["最终用户名称"]
                nvoicing_customer_province = row["省"]
                nvoicing_customer_city = row["市"]
                installation_address_province = row["省"]
                installation_address_city = row["市"]
                installation_address_district = row["区"]
                address = row["客户地址"]
                brand = row["分支机构"]
                customer_distributor = row["销售员"]
                telephone = row["联系电话"]
                liaison = row["联系人"]
                budget_department = row["预算部门"]
                use_department = row["使用部门"]
                other_brand = ""
                service_agent_name = ""
                row20_prepare_recommended_models_accessories = row["报备机型"]
                row21_number = row["台数"]
                row21_amount = row["预测金额"]
                row23_delivery_date = ""
                row23_declarations_number = ""
                remarks = ""
                ProvisionExcelModel(self.brand).to_excel_file(provision_date=provision_date,
                                                      invoicing_customer_name=invoicing_customer_name,latest_customer_name=latest_customer_name,
                                                      nvoicing_customer_province=nvoicing_customer_province,nvoicing_customer_city=nvoicing_customer_city,
                                                      installation_address_province=installation_address_province,installation_address_city=installation_address_city,
                                                      installation_address_district=installation_address_district,address=address,brand=brand,
                                                      customer_distributor=customer_distributor,telephone=telephone,liaison=liaison,
                                                      budget_department=budget_department,use_department=use_department,other_brand=other_brand,
                                                      service_agent_name=service_agent_name,row20_prepare_recommended_models_accessories=row20_prepare_recommended_models_accessories,
                                                      row21_number=row21_number,row21_amount=row21_amount,row23_delivery_date=row23_delivery_date,
                                                      row23_declarations_number=row23_declarations_number,remarks=remarks)
                new_latest_customer_name = re.sub(r'[\\/*?:<>|,.，。、"]', '_', latest_customer_name)
                file_name = f"{self.now_date}+{brand}+{new_latest_customer_name}.xlsx"
                provision_file_name_list.append(file_name)
                print(f"{file_name}")
        else:
            print(f"{self.now_date}没有客户需要第二次续报备")

        third_provision_df = third_time_provision_df[third_time_provision_df["重报（空档期终点）"] == self.now_date]
        if not third_provision_df.empty:
            print(f"{self.now_date}有{len(third_provision_df)}个客户需要新循环首次续报备")
            for index, row in third_provision_df.iterrows():
                provision_date = row["重报（空档期终点）"].strftime('%Y-%m-%d')
                invoicing_customer_name = row["开票客户名称"]
                latest_customer_name = row["最终用户名称"]
                nvoicing_customer_province = row["省"]
                nvoicing_customer_city = row["市"]
                installation_address_province = row["省"]
                installation_address_city = row["市"]
                installation_address_district = row["区"]
                address = row["客户地址"]
                brand = row["分支机构"]
                customer_distributor = row["销售员"]
                telephone = row["联系电话"]
                liaison = row["联系人"]
                budget_department = row["预算部门"]
                use_department = row["使用部门"]
                other_brand = ""
                service_agent_name = ""
                row20_prepare_recommended_models_accessories = row["报备机型"]
                row21_number = row["台数"]
                row21_amount = row["预测金额"]
                row23_delivery_date = ""
                row23_declarations_number = ""
                remarks = ""
                ProvisionExcelModel(self.brand).to_excel_file(provision_date=provision_date,
                                                    invoicing_customer_name=invoicing_customer_name,
                                                    latest_customer_name=latest_customer_name,
                                                    nvoicing_customer_province=nvoicing_customer_province,
                                                    nvoicing_customer_city=nvoicing_customer_city,
                                                    installation_address_province=installation_address_province,
                                                    installation_address_city=installation_address_city,
                                                    installation_address_district=installation_address_district,
                                                    address=address, brand=brand,
                                                    customer_distributor=customer_distributor, telephone=telephone,
                                                    liaison=liaison,
                                                    budget_department=budget_department, use_department=use_department,
                                                    other_brand=other_brand,
                                                    service_agent_name=service_agent_name,
                                                    row20_prepare_recommended_models_accessories=row20_prepare_recommended_models_accessories,
                                                    row21_number=row21_number, row21_amount=row21_amount,
                                                    row23_delivery_date=row23_delivery_date,
                                                    row23_declarations_number=row23_declarations_number,
                                                    remarks=remarks)
                new_latest_customer_name = re.sub(r'[\\/*?:<>|,.，。、"]', '_', latest_customer_name)
                file_name = f"{self.now_date}+{brand}+{new_latest_customer_name}.xlsx"
                provision_file_name_list.append(file_name)
                print(f"{file_name}")
        else:
            print(f"{self.now_date}没有客户需要新循环首次续报备")
        configs_toml = os.path.join(ROOT_DIR, 'settings', 'configs.toml')
        with open(configs_toml, "r", encoding="utf-8") as f:
            cfg = toml.load(f)
        cfg['BUSINESS'][f'{self.brand_name}ProcisionFileNameList'] = list(set(provision_file_name_list))
        with open(configs_toml, 'w', encoding="utf-8") as f:
            toml.dump(cfg, f)
# if __name__ == '__main__':
#     ContinuedProvision("C:/Users/Administrator/Desktop/广州商务组/理光报备汇总表.xlsx").provision_read_infoamtion()



